import { App, Body, Controller, Get, ILogger, Logger, Post } from "@midwayjs/core";
import { Application } from "../interface.js";
import { Query_dto } from "../models/query.dto.js";
import { getdb, getsqlit_db } from "../config/initdb.js";
import { Fail, failres, okres } from "../config/resUtil.js";

@Controller("/api/databaseui")
export class HomeController {
  @App("koa")
  app: Application;
  @Logger()
  logger: ILogger;

  //获取数据源
  //
  @Get("/getdatasourceoptions")
  async getdatasourceoptions() {
    var db = await getdb({
      id: 0,
      port: 0,
      user: "",
      password: "",
      database: "",
      type: "sqlite",
      host: "mydatabase.db"
    });
    var query = db("datasource").select();
    return okres(await query);
  }

  @Post("/getlist")
  async getlist(@Body() body: Query_dto): Promise<any> {
    var db = await getdb(body.datasourceDto);
    // var inert_result=await knex('tb_books').insert({title: 'Slaughterhouse Five'})
    var pageNum = body?.pageSize || 10;
    var tableName = body?.tableName;
    var offset = ((body?.pageNum || 1) - 1) * pageNum;
    if (!tableName) {
      return failres("[tableName] is required");
    }
    var queryForm = body?.queryForm;
    var searchKeys = Object.keys(queryForm);
    var queryBuilder = db.select().table(body.tableName);


    for (let i = 0; i < searchKeys?.length; i++) {
      if (searchKeys?.[i]) {
        queryBuilder.andWhere(searchKeys[i], "like", `%${queryForm?.[searchKeys[i]]}%`);
      }
    }

    var result1 = await queryBuilder.clone().limit(pageNum).offset(offset);
    var total = await queryBuilder.clone().count();
    return okres(result1, { total: total?.[0]?.["count(*)"] });
  }

  @Post("/tableproperty")
  async keydescription(@Body() body: Partial<Query_dto>): Promise<any> {
    var db = undefined;
    if (body?.datasourceDto?.database == "szq_erp") {
      db = this.app.erpdb;
    } else if (body?.datasourceDto?.database == "szq_mes") {
      db = this.app.mesdb;
    } else if (body?.datasourceDto?.database == "szq_system") {
      db = this.app.systemdb;
    } else {
      db = await getdb(body.datasourceDto);
    }
    // var inert_result=await knex('tb_books').insert({title: 'Slaughterhouse Five'})
    var pageNum = body?.pageSize || 10;
    var tableName = body?.tableName;
    var offset = ((body?.pageNum || 1) - 1) * pageNum;
    if (!tableName) {
      return failres("[tableName] is required");
    }
    var queryForm = body?.queryForm;
    var queryBuilder = await db.raw(`SHOW FULL COLUMNS FROM ${tableName};`);
    var onelist = queryBuilder?.[0];
    return okres(onelist);

  }

  @Post("/uppdatekeyvalue")
  async uppdatekeyvalue(@Body() body: Query_dto): Promise<any> {
    var db = await getdb(body.datasourceDto);
    // var inert_result=await knex('tb_books').insert({title: 'Slaughterhouse Five'})
    var pageNum = body?.pageSize || 10;
    var tableName = body?.tableName;
    var mainId = body?.mainId;
    var tableproperty = body?.tableproperty;
    var keyvalue = body?.keyvalue;
    var offset = ((body?.pageNum || 1) - 1) * pageNum;

    if (!tableName) {
      return failres("[tableName] is required");
    }
    if (!tableproperty) {
      return failres("[tableproperty] is required");
    }
    if (!mainId) {
      return failres("[mainId] is required");
    }
    var queryForm = body?.queryForm;
    var queryBuilder = await db.raw(`update ${tableName} set ${tableproperty}='${keyvalue}'  where id=${mainId}`);
    var onelist = queryBuilder?.[0];
    return okres(onelist);

  }

  @Post("/addrow")
  async addrow(@Body() body: Query_dto): Promise<any> {
    var db = await getdb(body.datasourceDto);
    // var inert_result=await knex('tb_books').insert({title: 'Slaughterhouse Five'})
    var pageNum = body?.pageSize || 10;
    var tableName = body?.tableName;
    var mainId = body?.mainId;
    var rowData = body?.rowData;
    var keyvalue = body?.keyvalue;
    var offset = ((body?.pageNum || 1) - 1) * pageNum;

    if (!tableName) {
      return failres("[tableName] is required");
    }
    if (!rowData) {
      return failres("[tableproperty] is required");
    }

    var queryForm = body?.queryForm;
    // knex('books').insert({title: 'Slaughterhouse Five'})
    var insertresult = await db(tableName).insert(rowData);
    this.logger.info("insertresult==>", insertresult);
    return okres(insertresult);

  }


  @Post("/gettablenames")
  async gettablenames(@Body() body: Query_dto): Promise<any> {
    var db = await getdb(body.datasourceDto);
    // var inert_result=await knex('tb_books').insert({title: 'Slaughterhouse Five'})
    var pageNum = body?.pageSize || 10;
    var tableName = body?.tableName;
    var offset = ((body?.pageNum || 1) - 1) * pageNum;
    /*
        if (!tableName) {
          return failres("[tableName] is required");
        }*/


    // var queryForm=body?.queryForm
    // knex('books').insert({title: 'Slaughterhouse Five'})
    if (body.datasourceDto.type == "sqlite") {
      var tablelistrs = await db.raw(`SELECT name FROM sqlite_master WHERE type='table' order by name`);
      // this.logger.info('[gettablenames] tablelistrs==>',tableFiler)
      var tablenamelist = (tablelistrs || []).map(item => item.name);
      return okres(tablenamelist);
    } else {
      var tablelistrs = await db.raw(`select table_name
  from
  information_schema.tables
  where
  table_schema='${body.datasourceDto?.database}'`);
      var tablelist = (tablelistrs && tablelistrs[0]).map(item => item["table_name"] || item["TABLE_NAME"]);
      if (tableName) {
        tablelist = tablelist.filter(key => key?.indexOf(tableName) >= 0);
      }
      // this.logger.info('[gettablenames] tablelistrs==>',tableFiler)
      return okres(tablelist);
    }


  }


//使用表面 描述 字段名称 获取信息
  @Post("/gettablebamesbytablenameorproperkeyordesc")
  async gettablebamesbytablenameorproperkeyordesc(@Body() body: Query_dto): Promise<any> {
    var db = await getdb(body.datasourceDto);
    // var inert_result=await knex('tb_books').insert({title: 'Slaughterhouse Five'})
    var pageNum = body?.pageSize || 10;
    var searchKey = body?.searchKey;
    if (body.datasourceDto.type == "sqlite") {
      var tablelistrs = await db.raw(`SELECT name FROM sqlite_master WHERE type='table' order by name`);
      var tablenamelist = (tablelistrs || []).map(item => item.name);
      if (searchKey) {
        tablenamelist = tablenamelist.filter(key => key?.indexOf(searchKey) >= 0);
      }
      return okres(tablenamelist);
    } else {
      var tablelistFilter = [];
      var tablelistrs = await db.raw(`select table_name
  from
  information_schema.tables
  where
  table_schema='${body.datasourceDto?.database}'`);
      var tablelist = (tablelistrs && tablelistrs[0]).map(item => item["table_name"] || item["TABLE_NAME"]);
      if (searchKey) {
        tablelistFilter = tablelist.filter(key => key?.indexOf(searchKey) >= 0).map(_tablename => {
          return {
            tableName: _tablename
          };
        });
      }

      for (let i = 0; i < tablelist.length; i++) {
      //字段名称
        var _tableName = tablelist[i];
        var keysresult = await this.keydescription({
          tableName: _tableName,
          datasourceDto: body?.datasourceDto
        });
        var keyslist = keysresult && keysresult.data || [];
        var hitReasons=''
        var keyexitinsearchkeys = keyslist.filter(keyitem => {
          if(keyitem.Field?.toLocaleLowerCase().indexOf(searchKey) >= 0){
            hitReasons+=`字段名称:${keyitem.Field?.toLocaleLowerCase()}  \n`
            return true
          }
        });
        var keyexitinsearchkey = keyexitinsearchkeys?.length >= 1;

        if (keyexitinsearchkey) {
          tablelistFilter.push({tableName:_tableName,hitReasons});
        }




        /*使用字段描述 命中*/
        var hitReasons2=''
        var keyexitinsearchkeys2 = keyslist.filter(keyitem => {
          var Comment = keyitem.Comment?.toLocaleLowerCase();
          // console.log("=>(databaseui.controller.ts:219) Comment", Comment,searchKey);
          if(Comment.indexOf(searchKey) >= 0){
            hitReasons2+=`字段备注：[${keyitem.Field?.toLocaleLowerCase()}] ${Comment}`
            return true
          }
        });
        var keyexitinsearchkey2 = keyexitinsearchkeys2?.length >= 1;
        if (keyexitinsearchkey2) {
          tablelistFilter.push({tableName:_tableName,hitReasons:hitReasons2});
        }
      }

      return okres(tablelistFilter);
    }


  }


}
